CentOS5 サーバー構築/データベース導入/MySQLデータベース自動バックアップ運用

MySQLデータベース自動バックアップ運用

概要:MySQLデータベースの自動バックアップを行なう。
ここでは、MySQL全データベースをサーバー内の別ディレクトリへバックアップする。

１. バックアップ設定

) バックアップスクリプト作成

# vi mysql-backup.sh　←　MySQLデータベースバックアップスクリプト作成

#!/bin/bash

PATH=/usr/local/sbin:/usr/bin:/bin

# バックアップ先ディレクトリ
BACKDIR=/backup/mysql

# MySQLrootパスワード
ROOTPASS=xxxxxxxx

# バックアップ先ディレクトリ再作成
rm  -rf $BACKDIR
mkdir -p $BACKDIR

# データベース名取得
DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /`

# データベースごとにバックアップ
for dbname in $DBLIST
do
    table_count=`mysql -u root -p$ROOTPASS -B -e "show tables" $dbname|wc -l`
    [ $table_count -ne 0 ] &&
    mysqlhotcopy $dbname -u root -p $ROOTPASS $BACKDIR | logger -t mysqlhotcopy
done

# chmod 700 mysql-backup.sh　←　rootのみ参照・実行できるようにパーミッション変更

) バックアップ確認

# ./mysql-backup.sh　←　MySQLデータベースバックアップスクリプト実行
※mysqlhotcopyが以下のエラーメッセージを出力して異常終了してしまう場合の対処
Invalid db.table name 'mysql.mysql`.`columns_priv' at /usr/bin/mysqlhotcopy line 855. 
# vi /usr/bin/mysqlhotcopy　←　/usr/bin/mysqlhotcopy編集
    my @dbh_tables = eval { $dbh->tables() };
    map { s/^.*?\.//o } @dbh_tables;　←　追加

# ll /backup/mysql/　←　MySQLデータベースバックアップ確認
合計 8
drwxr-x---  2 root root 4096 10月 28 21:27 mysql

) バックアップ定期自動実行設定

# echo "0 5 * * * root /root/mysql-backup.sh" > /etc/cron.d/backup
　　←　バックアップ定期自動実行設定追加

２. バックアップ・リストア確認(削除されたデータベースの復元)
削除されたデータベースをバックアップからリストア(復元)できるか確認する。

) テスト用データベース作成

# mysql -u root -p　←　MySQLへrootでログイン
Enter password: 　←　MySQLのrootパスワード応答
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28 to server version: 4.1.12

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test;　←　testデータベース作成
Query OK, 1 row affected (0.00 sec)

mysql> use test　←　testデータベースへ接続
Database changed
mysql> create table test(num int, name varchar(50));　←　testテーブル作成
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1,'鈴木一郎');　←　データ登録
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;　←　データ確認
+------+----------+
| num  | name     |
+------+----------+
|    1 | 鈴木一郎 |
+------+----------+
1 row in set (0.00 sec)

mysql> exit　←　ログアウト
Bye

) テスト用データベースバックアップ

# ./mysql-backup.sh　←　MySQLデータベースバックアップスクリプト実行

) テスト用データベース削除

# mysql -u root -p　←　MySQLへrootでログイン
Enter password: 　←　MySQLのrootパスワード応答
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35 to server version: 4.1.12

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test　←　testデータベースへ接続
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table test;　←　testテーブル削除
Query OK, 0 rows affected (0.01 sec)

mysql> drop database test;　←　testデータベース削除
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;　←　データベース削除確認
+----------+
| Database |
+----------+
| mysql    |
+----------+
1 rows in set (0.00 sec)

mysql> exit　←　ログアウト
Bye

) テスト用データベース復元

# /bin/cp -Rf /backup/mysql/test/ /var/lib/mysql/　←　バックアップよりtestデータベースをコピー
# chown -R mysql:mysql /var/lib/mysql/test/　←　testデータベースの所有者をmysqlに変更
# chmod 700 /var/lib/mysql/test/　←　testデータベースのパーミッションを700に変更
# chmod 660 /var/lib/mysql/test/*　←　testデータベース内データのパーミッションを660に変更

) テスト用データベース復元確認

# mysql -u root -p　←　MySQLへrootでログイン
Enter password: 　←　MySQLのrootパスワード応答
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;　←　testデータベース復元確認
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql> use test　←　testデータベースへ接続
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;　←　testテーブル復元確認
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysql> select * from test;　←　データ復元確認
+------+----------+
| num  | name     |
+------+----------+
|    1 | 鈴木一郎 |
+------+----------+
1 row in set (0.00 sec)

mysql> drop table test;　←　testテーブル削除
Query OK, 0 rows affected (0.00 sec)

mysql> drop database test;　←　testデータベース削除
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;　←　testデータベース削除確認
+----------+
| Database |
+----------+
| mysql    |
+----------+
1 rows in set (0.00 sec)

mysql> exit　←　ログアウト
Bye

３. バックアップ・リストア確認(変更されたデータベースの復元)
変更されたデータベースをバックアップからリストア(復元)できるか確認する

) テスト用データベース作成

# mysql -u root -p　←　MySQLへrootでログイン
Enter password: 　←　MySQLのrootパスワード応答
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 4.1.12

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test;　←　testデータベース作成
Query OK, 1 row affected (0.00 sec)

mysql> use test　←　testデータベースへ接続
Database changed
mysql> create table test(num int, name varchar(50));　←　testテーブル作成
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1,'山田太郎');　←　データ登録
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;　←　データ確認
+------+----------+
| num  | name     |
+------+----------+
|    1 | 鈴木一郎 |
+------+----------+
1 row in set (0.00 sec)

mysql> exit　←　ログアウト
Bye

) テスト用データベースバックアップ

# ./mysql-backup.sh　←　MySQLデータベースバックアップスクリプト実行

) テスト用データベース変更

# mysql -u root -p　←　MySQLへrootでログイン
Enter password: 　←　MySQLのrootパスワード応答
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25 to server version: 4.1.12

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test　←　testデータベースへ接続
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update test set name='鈴木次郎';　←　データ変更
mysql> update test set name='鈴木三郎';　←　データ変更
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;　←　データ確認
+------+----------+
| num  | name     |
+------+----------+
|    1 | 鈴木次郎 |
+------+----------+
1 row in set (0.00 sec)

mysql> exit　←　ログアウト
Bye

) テスト用データベース復元

# /bin/cp -Rf /backup/mysql/test/ /var/lib/mysql/　←　バックアップよりtestデータベースをコピー

) テスト用データベース復元確認

# mysql -u root -p　←　MySQLへrootでログイン
Enter password: 　←　MySQLのrootパスワード応答
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26 to server version: 4.1.12

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test　←　testデータベースへ接続
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test;　←　データ復元確認
+------+----------+
| num  | name     |
+------+----------+
|    1 | 鈴木一郎 |
+------+----------+
1 row in set (0.00 sec)

mysql> drop table test;　←　testテーブル削除
Query OK, 0 rows affected (0.00 sec)

mysql> drop database test;　←　testデータベース削除
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;　←　testデータベース削除確認
+----------+
| Database |
+----------+
| mysql    |
+----------+
1 rows in set (0.00 sec)

mysql> exit　←　ログアウト
Bye

前へ上層へ一覧へ次へ

Last-modified: 2014-03-11 (火) 01:59:57 (3701d)